<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@page import="connection.DBConnection"%>
<%@page import="java.util.*" %>
<%@page import="java.text.SimpleDateFormat" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Customer Ledger Report</title>
</head>
<body>

<form name="customerledgerreport"  action="CustomerLedgerPeriodicServlet">

   <%
        String exportToExcel = request.getParameter("exportToExcel");
        if (exportToExcel != null && exportToExcel.toString().equalsIgnoreCase("YES")) 
        {
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "inline; filename=" + "CustomerLedgerReport.xls");
 
        }
    %>


<%        		String selectedaccname = (String) request.getAttribute("accountname");
				String selectedacccode = (String) request.getAttribute("accountcode");
				String selectedaccgroup = (String) request.getAttribute("accountgroup");	
%>

<h2 align="center"><%=selectedaccname %> Customer Ledger Report</h2>

<table align="center" border=5  width=600>

<%  DBConnection dbc=new DBConnection();
			Connection con=dbc.getNewConnection();
			Statement st = null;
			ResultSet rs = null;
			
			int count=1;
			double debtors=0, creditors=0, cashbank=0, fixedassets=0, other=0, capital=0, otherexpenses=0, sellingandmarketing=0, salaryandwages=0, administrativecharges=0, provision=0, unsecuredloans=0, securedloans=0, investments=0, closingstock=0, openingstock=0, loansandadvances=0, indirectincome=0, bankchargesandinterest=0, depreciation=0; //trial balance
			
			double voucher=0.0,v3=0.0;
			
			String v5="",b5="",d="",d1="",d2="",d3="",min="",max="";
			%>
			<%
			try
			{
				  st=con.createStatement() ;
				  rs=st.executeQuery("SELECT MIN(DATE) FROM ( (SELECT date as DATE FROM salesvoucher2) UNION ALL (SELECT  date as DATE FROM CreditNote) UNION ALL (SELECT date as DATE FROM receiptsvoucher2) UNION ALL (SELECT date as DATE FROM paymentvoucher2) UNION ALL (SELECT date as DATE FROM journalvoucher2)) p");
			
			while(rs.next()){
			
						 			
						        min=rs.getString(1);
			 } 
   		  	
     		  } 
          catch(Exception e) {}//code	        
						     %>
						     
						     
						     <%
			try
			{
				  st=con.createStatement() ;
				  rs=st.executeQuery("SELECT MAX(DATE) FROM ( (SELECT date as DATE FROM salesvoucher2) UNION ALL (SELECT  date as DATE FROM CreditNote) UNION ALL (SELECT date as DATE FROM receiptsvoucher2) UNION ALL (SELECT date as DATE FROM paymentvoucher2) UNION ALL (SELECT date as DATE FROM journalvoucher2)) p");
			
			while(rs.next()){
			
						 			
						        max=rs.getString(1);
			 } 
   		  	
     		  } 
          catch(Exception e) {}//code	        
						     %>
						     
						     <tr>
						    <td colspan=5> From:<%=min %> <br>  
						     To:<%=max %>
						     </tr>
<tr>
		<td colspan=5 align=center> BOA  </td>
</tr>
 

 
<tr>
		<th> Date </th>
		<th> Particulars </th>
		<th> Voucher type </th>
 		<th> Voucher Number </th>
 		<th> Amount </th>
</tr>

<% 

		
		double total=0.0;
		double as14=0.0, as24=0.0, ar14=0.0, ar24=0.0, ap14=0.0, ap24=0.0, aj14=0.0, aj24=0.0, cd14=0.0;  %>
   		
<input type="hidden" name="accname" value="<%=selectedaccname%>"/>
<input type="hidden" name="acccode" value="<%=selectedacccode%>"/>
<input type="hidden" name="accgroup" value="<%=selectedaccgroup%>"/>


	
	
	<%	try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT innum,date,scname,stota FROM SalesVoucher2 where scname='"+selectedaccname+"' OR ccode='"+selectedacccode+"';");
		   
		   while(rs.next())
		   {
		        String s1=rs.getString(1); //sales voucher number      
		        String sdate2=rs.getString(2); //date
		        String s3=rs.getString(3); //account name
				double s4=rs.getDouble(4); //amount
				
				as14=as14+s4;
%>


<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String s2 =formatter.format(fromsql.parse(sdate2));
            		%>
			<td> <%=s2 %> </td>
			<td> Total Sales </td>
			<td> Sales </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=s1%>&group=Sales"> <%=s1 %> </a> </td>
			<td> <%=s4 %> </td>	
</tr>
<%		   
		   }
   }
   catch(Exception e)
   {}
%>

	<%	try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT cdinno,date,cdcname,-(cdtotal) FROM CreditNote where cdcname='"+selectedaccname+"' OR ccode='"+selectedacccode+"';");
		   
		   while(rs.next())
		   {
		        String s1=rs.getString(1); //sales voucher number      
		        String sdate2=rs.getString(2); //date
		        String s3=rs.getString(3); //account name
				double s4=rs.getDouble(4); //amount
				
				cd14=cd14+s4;
%>


<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String s2 =formatter.format(fromsql.parse(sdate2));
            		%>
			<td> <%=s2 %> </td>
			<td> Total Credit </td>
			<td> Credit </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=s1%>&group=Credit"> <%=s1 %> </a> </td>
			<td> <%=s4 %> </td>	
</tr>
<%		   
		   }
   }
   catch(Exception e)
   {}
%>


<%
		try
   		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT renum,date,bname,-(ramount) FROM ReceiptsVoucher2 where raccname='"+selectedaccname+"' OR raccid='"+selectedacccode+"';");
		   
		   while(rs.next())
		   {
		        String r11=rs.getString(1); //receipts voucher number	        
		        String rdate12=rs.getString(2); //date
		        String r13=rs.getString(3); //bank name
				double r14=rs.getDouble(4); //amount
				
				ar14=ar14+r14;
%>
<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String r12 =formatter.format(fromsql.parse(rdate12));
            		%>
			<td> <%=r12 %> </td>
			<td> <%=r13 %> </td>
			<td> Receipts </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=r11%>&group=Receipts"> <%=r11 %> </a></td>
			<td> <%=r14 %> </td>	
</tr>
<%		   
		   }
   }
   catch(Exception e)
   {}
%>

<%--
<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT renum,date,bname,-(ramount2) FROM ReceiptsVoucher2 where raccname2='"+selectedaccname+"' AND racc2id='"+selectedacccode+"';");
		   
		   while(rs.next())
		   {
		        String r21=rs.getString(1); //receipts voucher number	        
		        String rdate22=rs.getString(2); //date
		        String r23=rs.getString(3); //account name
				double r24=rs.getDouble(4); //amount22
				
				ar24=ar24+r24;
%>
<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String r22 =formatter.format(fromsql.parse(rdate22));
            		%>
			<td> <%=r22 %> </td>
			<td> <%=r23 %> </td>
			<td> Receipts </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=r21%>&group=Receipts"> <%=r21 %> </a> </td>
			<td> <%=r24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>
 --%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pyenum,date,bname,pyamount FROM PaymentVoucher2 where pyaccname='"+selectedaccname+"' OR pyaccid='"+selectedacccode+"';");
		   
		   while(rs.next())
		   {
		        String p11=rs.getString(1); //Payment voucher number	        
		        String pdate12=rs.getString(2); //date
		        String p13=rs.getString(3); //bank name
				double p14=rs.getDouble(4); //amount
				
				ap14=ap14+p14;
%>
<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String p12 =formatter.format(fromsql.parse(pdate12));
            		%>
			<td> <%=p12 %> </td>
			<td> <%=p13 %> </td>
			<td> Payment </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=p11%>&group=Payment"> <%=p11 %> </a> </td>
			<td> <%=p14 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

<%--
<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT pyenum,date,bname,pyamount2 FROM PaymentVoucher2 where pyaccname2='"+selectedaccname+"' AND pyacc2id='"+selectedacccode+"';");
		   
		   while(rs.next())
		   {
		        String p21=rs.getString(1); //Payment voucher number	        
		        String pdate22=rs.getString(2); //date
		        String p23=rs.getString(3); //bank name
				double p24=rs.getDouble(4); //amount
				
				ap24=ap24+p24;
%>
<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String p22 =formatter.format(fromsql.parse(pdate22));
            		%>
			<td> <%=p22 %> </td>
			<td> <%=p23 %> </td>
			<td> Payment </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=p21%>&group=Payment"> <%=p21 %> </a></td>
			<td> <%=p24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>
 --%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT jname,date,jaccdeb,jamount1 FROM JournalVoucher2 where jaccdeb='"+selectedaccname+"' OR jaccid='"+selectedacccode+"';");
		   
		   while(rs.next())
		   {
		        String j11=rs.getString(1); //Journal voucher number	        
		        String jdate12=rs.getString(2); //date
		        String j13=rs.getString(3); //account name
				double j14=rs.getDouble(4); //amount
				
				aj14=aj14+j14;
%>
<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String j12 =formatter.format(fromsql.parse(jdate12));
            		%>
			<td> <%=j12 %> </td>
			<td> <%=j13 %> </td>
			<td> Journal </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=j11%>&group=Journal"> <%=j11 %> </a></td>
			<td> <%=j14 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

<%
		try
		{
		   st=con.createStatement();
		   rs=st.executeQuery("SELECT jname,date,jacccred,-(jamount2) FROM JournalVoucher2 where jacccred='"+selectedaccname+"' OR jacc2id='"+selectedacccode+"';");
		   
		   while(rs.next())
		   {
		        String j21=rs.getString(1); //Journal voucher number	        
		        String jdate22=rs.getString(2); //date
		        String j23=rs.getString(3); //account name
				double j24=rs.getDouble(4); //amount
				
				aj24=aj24+j24;
%>
<tr>
			        <%  
           	    		 SimpleDateFormat fromsql = new SimpleDateFormat("yyyy-MM-dd");
        				 SimpleDateFormat formatter = new SimpleDateFormat("dd-MM-yyyy");
        		 		 String j22 =formatter.format(fromsql.parse(jdate22));
            		%>
			<td> <%=j22 %> </td>
			<td> <%=j23 %> </td>
			<td> Journal </td>
			<td> <a href="http://localhost:8081/BOA_Web/LedgerReportServlet?vouchernumber=<%=j21%>&group=Journal"> <%=j21 %> </a> </td>
			<td> <%=j24 %> </td>	
</tr>
<%		   
		   }
		}
		catch(Exception e)
		{}
%>

	<% total=as14+ar14+ap14+aj14+aj24+cd14; %>
<tr>
		<td> </td>
		<td> Total </td>
		<td> </td>
		<td> </td>
		<td> <%=total %></td>
</tr>

</table>

<br><br> 
	&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

    <%
        if (exportToExcel == null) {
    %>
    <a href="CustomerLedgerReport.jsp?exportToExcel=YES">Export to Excel</a>
    <%
        }
    %>

<br><br>
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp &nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
From <input type=text name=fromdate > To <input type=text name=todate >
		 <input type=submit name="datewise" value="Date Wise">
</form>
</body>
</html>